﻿CREATE OR REPLACE FUNCTION public.upload_bamqc(filename text, bamqc_table text DEFAULT 'bamqc'::text)
  RETURNS VOID AS
$BODY$
/*
Upload the output of bamqc.py to a target table.
Duplicate rows or rows already in the target table are skipped therefore
it is fine to import files with redundant rows.

See also http://code.google.com/p/bioinformatics-misc/source/browse/trunk/bamqc.py

ARGUMENTS:
    filename: File containing the bamqc output to import.
              Without header. No default required 

    bamqc_table: Append to this table. Default is bamqc

REQUIREMENTS:
    Funtion read_table. See http://code.google.com/p/postgresql-read-table/source/browse/trunk/read_table-0.97-alpha-py3.sql
*/
BEGIN
-- Import data file
EXECUTE 'CREATE TABLE bamqc_upload_tmp (LIKE ' || quote_ident(bamqc_table) || ' INCLUDING DEFAULTS)';
EXECUTE 'COPY bamqc_upload_tmp FROM ' || quote_literal(filename) || $del$ WITH CSV DELIMITER E'\t'$del$;
--'SELECT read_table($$ file: ' || quote_literal(filename) || ', header:False, temp:False, table: "bamqc_upload_tmp", overwrite:True $$)';
-- Remove duplicates
EXECUTE 'INSERT INTO ' || quote_ident(bamqc_table) ||
    ' SELECT DISTINCT * FROM bamqc_upload_tmp
      EXCEPT
      SELECT * FROM ' || quote_ident(bamqc_table); 
EXECUTE 'DROP TABLE bamqc_upload_tmp';
END
$BODY$
  LANGUAGE plpgsql VOLATILE;